import os.path

import pandas as pd
import numpy as np

from shared_variables import CONN


def create_projects_table():
    cursor = CONN.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS projects (
            id INTEGER PRIMARY KEY,
            name TEXT,
            bonus INTEGER,
            paymentGap INTEGER,
            duration INTEGER,
            describe TEXT,
            first_words TEXT
        )
    ''')
    CONN.commit()


def create_people_table():
    cursor = CONN.cursor()

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS people (
            id INTEGER PRIMARY KEY,
            name TEXT,
            describe TEXT,
            cash INTEGER,
            salary INTEGER,
            energy INTEGER,
            rent_expense INTEGER,
            living_expense INTEGER,
            social_expense INTEGER,
            avatar TEXT,
            portrait TEXT
        )
    ''')

    CONN.commit()


def create_talks_table():
    cursor = CONN.cursor()

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS talks (
            id INTEGER PRIMARY KEY,
            project_id INTEGER,
            speak_id INTEGER,
            speak_time DATETIME,
            content TEXT,
            FOREIGN KEY(project_id) REFERENCES projects(id),
            FOREIGN KEY(speak_id) REFERENCES people(id)
        )
    ''')

    CONN.commit()


def create_project_role_link_table():
    cursor = CONN.cursor()

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS role_link (
            project_id INTEGER,
            people_id INTEGER,
            role TEXT,
            FOREIGN KEY(project_id) REFERENCES projects(id),
            FOREIGN KEY(people_id) REFERENCES people(id)
        )
    ''')

    CONN.commit()


def create_task_table():
    cursor = CONN.cursor()

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS task (
            id INTEGER PRIMARY KEY,
            name TEXT,
            describe TEXT,
            bonus INTEGER,
            cost INTEGER,
            is_critical BOOLEAN
        )
    ''')

    CONN.commit()


def create_relationships_table():
    cursor = CONN.cursor()

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS relationships (
            peopleAB TEXT,
            relationship TEXT
        )
    ''')

    CONN.commit()


def create_taskPeopleLinks_table():
    cursor = CONN.cursor()

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS taskPeopleLinks (
            project_id INTEGER,
            task_id INTEGER,
            people_id INTEGER,
            FOREIGN KEY(project_id) REFERENCES projects(id),
            FOREIGN KEY(task_id) REFERENCES task(id),
            FOREIGN KEY(people_id) REFERENCES people(id)
        )
    ''')

    CONN.commit()


def create_projectTaskLinks_table():
    cursor = CONN.cursor()

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS projectTaskLinks (
            project_id INTEGER,
            task_id INTEGER,
            FOREIGN KEY(project_id) REFERENCES projects(id),
            FOREIGN KEY(task_id) REFERENCES task(id)
        )
    ''')

    CONN.commit()


def create_goods_table():
    cursor = CONN.cursor()

    cursor.execute('''
        CREATE TABLE IF NOT EXISTS goods (
            id INTEGER PRIMARY KEY,
            name TEXT,
            cost INTEGER,
            bonus INTEGER,
            describe TEXT
        )
    ''')

    CONN.commit()


def init_all_tables():
    create_projects_table()
    create_talks_table()
    create_project_role_link_table()
    create_people_table()
    create_task_table()
    create_relationships_table()
    create_taskPeopleLinks_table()
    create_projectTaskLinks_table()
    create_goods_table()


def init_data():
    # 定义CSV文件和相应的表名
    csv_files = {
        'people.csv': 'people',
        'project.csv': 'project',
        'projectTaskLinks.csv': 'projectTaskLinks',
        'task.csv': 'task'
    }
    # 遍历CSV文件和对应的表名
    for csv_file, table_name in csv_files.items():
        # 从 CSV 文件创建 DataFrame
        df = pd.read_csv(os.path.join('./data/init_data/', csv_file))

        # 将 DataFrame 写入数据库表中
        df.to_sql(table_name, CONN, if_exists='replace', index=False)


def generate_relationships():
    data = pd.read_csv('./data/init_data/people.csv')
    num_people = data.shape[0]
    relationships = []

    relationship_mapping = {
        0: "死对头",
        1: "相互不喜欢",
        2: "不熟",
        3: "喜欢",
        4: "密友"
    }
    for i in range(num_people):
        for j in range(i + 1, num_people):
            personA = data.loc[i, 'name']
            personB = data.loc[j, 'name']
            relationship = np.random.randint(0, 5)
            key = ','.join(sorted([personA, personB]))
            relationships.append((key, relationship_mapping[relationship]))

    df = pd.DataFrame(relationships, columns=['peopleAB', 'relationship'])
    df.to_sql("relationships", CONN, if_exists='replace', index=False)


if __name__ == '__main__':
    init_all_tables()
    init_data()
    generate_relationships()
